"""
Database Configuration Module
Database connection and session management - PostgreSQL direct connection, no Redis dependency
数据库连接和会话管理 - PostgreSQL直连，无Redis依赖
"""

import os
from typing import AsyncGenerator, Optional

from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine, async_sessionmaker
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy import MetaData, text
import structlog

# 配置结构化日志
logger = structlog.get_logger(__name__)

# 数据库命名约定
convention = {
    "ix": "ix_%(column_0_label)s",
    "uq": "uq_%(table_name)s_%(column_0_name)s",
    "ck": "ck_%(table_name)s_%(constraint_name)s",
    "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
    "pk": "pk_%(table_name)s",
}

metadata = MetaData(naming_convention=convention)


class Base(DeclarativeBase):
    """数据库模型基类"""

    metadata = metadata


# 数据库配置将使用设置中的值，这里定义默认值
default_database_url = "postgresql+asyncpg://media_user:password@localhost:5432/media_create_db"

# 全局引擎和会话工厂（将在运行时初始化）
engine = None
AsyncSessionLocal = None


def init_database_engine(database_url: str = None, debug: bool = False):
    """初始化数据库引擎 - 使用提供的设置"""
    global engine, AsyncSessionLocal
    
    url = database_url or default_database_url
    
    # 创建异步数据库引擎 - 优化版本
    engine = create_async_engine(
        url,
        echo=debug,
        pool_size=50,  # 增加连接池大小
        max_overflow=100,  # 增加溢出连接数
        pool_timeout=60,  # 增加连接超时时间
        pool_pre_ping=True,  # 连接池预检查
        pool_recycle=1800,  # 连接回收时间 - 更频繁的连接刷新
        pool_use_lifo=True,  # 使用LIFO策略提高连接复用率
        execution_options={
            "isolation_level": "READ_COMMITTED",  # 设置合适的事务隔离级别
            "compiled_cache": {},  # 启用编译缓存
        },
    )
    
    # 创建会话工厂 - 优化版本
    AsyncSessionLocal = async_sessionmaker(
        engine,
        class_=AsyncSession,
        expire_on_commit=False,  # 提高性能，避免不必要的查询
        autoflush=False,  # 禁用自动刷新，手动控制
        autocommit=False,  # 禁用自动提交
        future=True,  # 启用未来兼容模式
    )
    
    logger.info("数据库引擎初始化完成", url=url)


async def get_db() -> AsyncGenerator[AsyncSession, None]:
    """
    获取数据库会话

    Yields:
        AsyncSession: 异步数据库会话
    """
    async with AsyncSessionLocal() as session:
        try:
            yield session
        except Exception as e:
            logger.error("数据库会话错误", error=str(e))
            await session.rollback()
            raise
        finally:
            await session.close()


async def init_db() -> None:
    """
    初始化数据库
    创建所有表结构
    """
    try:
        logger.info("开始初始化数据库")
        async with engine.begin() as conn:
            await conn.run_sync(Base.metadata.create_all)
        logger.info("数据库初始化完成")
    except Exception as e:
        logger.error("数据库初始化失败", error=str(e))
        raise


async def drop_db() -> None:
    """
    删除所有表（仅用于测试/开发）
    """
    try:
        logger.warning("开始删除数据库表")
        async with engine.begin() as conn:
            await conn.run_sync(Base.metadata.drop_all)
        logger.warning("数据库表删除完成")
    except Exception as e:
        logger.error("数据库删除失败", error=str(e))
        raise


async def check_db_connection() -> bool:
    """
    检查数据库连接

    Returns:
        bool: 连接是否成功
    """
    # 检查引擎是否已初始化
    if engine is None:
        logger.error("数据库引擎未初始化")
        return False
        
    try:
        async with engine.connect() as conn:
            result = await conn.execute(text("SELECT 1"))
            value = result.scalar()
            # 不需要await value，因为它已经是一个普通值
        logger.info("数据库连接检查通过")
        return True
    except Exception as e:
        logger.error("数据库连接检查失败", error=str(e))
        return False


async def get_db_stats() -> dict:
    """
    获取数据库统计信息

    Returns:
        dict: 数据库统计信息
    """
    try:
        async with AsyncSessionLocal() as session:
            # 获取表数量
            result = await session.execute(
                text("""
                SELECT COUNT(*) 
                FROM information_schema.tables 
                WHERE table_schema = 'public' 
                AND table_type = 'BASE TABLE'
                """)
            )
            table_count = result.scalar()

            # 获取数据库大小
            result = await session.execute(
                text("SELECT pg_database_size(current_database())")
            )
            db_size = result.scalar()

            return {
                "table_count": table_count,
                "database_size_bytes": db_size,
                "database_size_mb": round(db_size / (1024 * 1024), 2),
                "connection_pool_size": DB_POOL_SIZE,
                "max_overflow": DB_MAX_OVERFLOW,
            }
    except Exception as e:
        logger.error("获取数据库统计信息失败", error=str(e))
        return {}


# 数据库会话上下文管理器
class DatabaseSession:
    """数据库会话上下文管理器"""

    def __init__(self):
        self.session: Optional[AsyncSession] = None

    async def __aenter__(self) -> AsyncSession:
        self.session = AsyncSessionLocal()
        return self.session

    async def __aexit__(self, exc_type, exc_val, exc_tb):
        if exc_type is not None:
            await self.session.rollback()
        else:
            await self.session.commit()
        await self.session.close()


# 导出主要组件
__all__ = [
    "Base",
    "init_database_engine",
    "get_db",
    "init_db",
    "drop_db",
    "check_db_connection",
    "get_db_stats",
    "DatabaseSession",
]